﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using HotelSoft.HotelManagement_DAO;
using HotelSoft.HotelManagement_DTO;
using System.Data.OleDb;
using System.Configuration;

namespace HotelSoft.HotelManagement_DAO
{
    public class SqlDataAccessHelper : DataAccessHelper
    {
        protected OleDbDataAdapter adapter;
        protected OleDbConnection connection;
      
        public SqlDataAccessHelper()
        {
            strConnection =  GetConnectionString();
        }

        public string GetConnectionString()        
        {
            return ConfigurationManager.AppSettings["DBCon"];   
        }   

        public void ChangeConnectionString()
        {
            string str = ConfigurationSettings.AppSettings["DBCon"];
            System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            if (str == null)
            {
                config.AppSettings.Settings.Add("DBCon",strConnection);
            }
            else
            {
                config.AppSettings.Settings.Remove("DBCon");
                config.AppSettings.Settings.Add("DBCon", strConnection);
            }

            config.Save(ConfigurationSaveMode.Modified);
            ConfigurationManager.RefreshSection("appSettings");
        }

        public void SqlData(object AbstractDTO )
        {
            SqlDataDTO dto = (SqlDataDTO)AbstractDTO;
            if(dto.TRUSTED)
                strConnection = @"Data Source="+dto.SERVERNAME+";Initial Catalog="+dto.DATABASE+";Integrated Security=true";
            else
                strConnection = @"Data Source=" + dto.SERVERNAME + ";Initial Catalog=" + dto.DATABASE + ";User Id="+dto.USERNAME+";Password="+dto.PASSWORD;
        }

        public bool doLogin()
        {
            SqlConnection conn = new SqlConnection();
            // TODO: Modify the connection string
            conn.ConnectionString = strConnection;
            try
            {
                conn.Open();
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }


        public override DataTable ExecuteQuery(string strQuery)
        {
            DataTable resTable = new DataTable();

            SqlConnection _con = new SqlConnection(strConnection);
            try
            {
                _con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(strQuery, _con);
                adapter.Fill(resTable);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi thực thi lệnh SQL: " + ex.Message);
            }
            finally
            {
                _con.Close();
            }

            return resTable;
        }

        public override void ExecuteNonQuery(string strQuery)
        {
            SqlConnection _con = new SqlConnection(strConnection);

            try
            {
                _con.Open();
                SqlCommand command = _con.CreateCommand();
                command.CommandText = strQuery;
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi thực thi lệnh SQL: " + ex.Message);
            }
            finally
            {
                _con.Close();
            }
        }

        public override bool ExecuteScalar(string strQuery)
        {
            SqlConnection _con = new SqlConnection(strConnection);

            try
            {
                _con.Open();
                SqlCommand command = _con.CreateCommand();
                command.CommandText = strQuery;
                int So = (int)command.ExecuteScalar();
                if (So > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi thực thi lệnh SQL: " + ex.Message);
            }
            finally
            {
                _con.Close();
            }
        }

        //public override DataTable ExecuteProcedure(string storeProcName, Dictionary<string, object> para)
        //{
        //    DataTable resTable = new DataTable();
        //    SqlConnection _con = new SqlConnection(strConnection);
        //    try
        //    {
        //        _con.Open();

        //        SqlCommand cmd = new SqlCommand();
        //        cmd.Connection = _con;
        //        cmd.CommandText = storeProcName;
        //        cmd.CommandType = CommandType.StoredProcedure;

        //        foreach (KeyValuePair<string, object> pair in para)
        //        {
        //            SqlParameter sqlParam = new SqlParameter(pair.Key, pair.Value);
        //            cmd.Parameters.Add(sqlParam);
        //        }

        //        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        //        adapter.Fill(resTable);

        //        _con.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
        //    }

        //    return resTable;
        //}

        //public  DataTable queryObject(String sqlString)
        //{
        //    adapter = new OleDbDataAdapter(sqlString, connection);
        //    DataTable kq = new DataTable();
        //    adapter.Fill(kq);
        //    return kq;
        //}
    }
}
